#!/usr/bin/env python
# coding=utf-8
# 将网络层数据写入数据库

import MySQLdb
import os
import json


def network():
	# 连接数据库
	conn = MySQLdb.connect(
	                host='localhost',
	                port=3306,
	                user='root',
	                passwd='cnv6201',
	                db='PRESENTATION',
	                charset='utf8',
	            )
	cur = conn.cursor()
	# 清空数据库中表格内的信息
	cur.execute("delete from presentation_network_s1_ip_before")
	cur.execute("delete from presentation_network_s1_ip_after")
	cur.execute("delete from presentation_network_s2_ip_before")
	cur.execute("delete from presentation_network_s2_ip_after")
	cur.execute("delete from presentation_network_route_before")
	cur.execute("delete from presentation_network_route_after")

	# 读取网络状态信息
	f = open('JsonFileReport/report_network_before.json', "r", encoding='UTF-8')
	n = json.load(f)

	for i in range(len(n['layer_info'])):
	    if n['layer_info'][i]['hostname'] == "S2":
	        ip = n['layer_info'][i]['ip']
	        ser_port = n['layer_info'][i]['ser_port']
	        phy_port = n['layer_info'][i]['phy_port']
	        vip = n['layer_info'][i]['vip']
	        vport = n['layer_info'][i]['vport']
	        prtcode = n['layer_info'][i]['prtcode']
	        tcpoption = n['layer_info'][i]['tcpoption']
	        cur.execute(
	            "insert into presentation_network_s2_ip_before(ip, ser_port, phy_port, vip, vport, prtcode, tcpoption) "
	            "values(%s,%s,%s,%s,%s,%s,%s)",
	            [ip, ser_port, phy_port, vip, vport, prtcode, tcpoption])
	    elif n['layer_info'][i]['hostname'] == "S1":
	        ip = n['layer_info'][i]['ip']
	        ser_port = n['layer_info'][i]['ser_port']
	        phy_port = n['layer_info'][i]['phy_port']
	        vip = n['layer_info'][i]['vip']
	        vport = n['layer_info'][i]['vport']
	        prtcode = n['layer_info'][i]['prtcode']
	        tcpoption = n['layer_info'][i]['tcpoption']
	        cur.execute(
	            "insert into presentation_network_s1_ip_before(ip, ser_port, phy_port, vip, vport, prtcode, tcpoption) "
	            "values(%s,%s,%s,%s,%s,%s,%s)",
	            [ip, ser_port, phy_port, vip, vport, prtcode, tcpoption])

	for i in range(len(n['routes_info'])):
	    i = i+1
	    route = "->".join(n['routes_info']['route' + str(i)])
	    cur.execute("insert into presentation_network_route_before(route) " "values(%s)", [route])

	f.close()




	d = open('JsonFileReport/report_network_after.json', "r", encoding='UTF-8')
	m = json.load(d)

	for i in range(len(m['layer_info'])):
	    if m['layer_info'][i]['hostname'] == "S2":
	        ip = m['layer_info'][i]['ip']
	        ser_port = m['layer_info'][i]['ser_port']
	        phy_port = m['layer_info'][i]['phy_port']
	        vip = m['layer_info'][i]['vip']
	        vport = m['layer_info'][i]['vport']
	        prtcode = m['layer_info'][i]['prtcode']
	        tcpoption = m['layer_info'][i]['tcpoption']
	        cur.execute(
	            "insert into presentation_network_s2_ip_after(ip, ser_port, phy_port, vip, vport, prtcode, tcpoption) "
	            "values(%s,%s,%s,%s,%s,%s,%s)",
	            [ip, ser_port, phy_port, vip, vport, prtcode, tcpoption])
	    elif m['layer_info'][i]['hostname'] == "S1":	
	        ip = m['layer_info'][i]['ip']
	        ser_port = m['layer_info'][i]['ser_port']
	        phy_port = m['layer_info'][i]['phy_port']
	        vip = m['layer_info'][i]['vip']
	        vport = m['layer_info'][i]['vport']
	        prtcode = m['layer_info'][i]['prtcode']
	        tcpoption = m['layer_info'][i]['tcpoption']
	        cur.execute(
	            "insert into presentation_network_s1_ip_after(ip, ser_port, phy_port, vip, vport, prtcode, tcpoption) "
	            "values(%s,%s,%s,%s,%s,%s,%s)",
	            [ip, ser_port, phy_port, vip, vport, prtcode, tcpoption])


	for i in range(len(m['routes_info'])):
	    i = i+1
	    route = "->".join(m['routes_info']['route' + str(i)])
	    cur.execute("insert into presentation_network_route_after(route) " "values(%s)", [route])


	d.close()


	cur.close()
	conn.commit()
	conn.close()
	print("network 层数据存储成功")
